﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using bmapi.HelperClass;
using bmapi.Models.DataEntity;
using bmapi.SQLServerAccess;
using static bmapi.Models.ShowModel;

namespace bmapi.Controllers
{
    public class StatisticsController : PageController
    {
        /// <summary>
        /// 往年统计图
        /// </summary>
        /// <param name="year">年份 要求格式</param>
        /// <returns></returns>
        [HttpGet, Route("PastYearStatistics")]
        public HttpResponseMessage FormerYearMap(string year)
        {
            string sql = "";
            //查询出一年当中样板数，生产单数，件数，床数，铺布总长度,特体Pod数的节点
            sql = "select model_count,order_count,number,bed,len,pod from Stats_tb where DATEPART(YEAR,day)=" + year + "";
            SQLCommand sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            //定义存储对象的数组
            List<double> modelcount = new List<double>();
            List<double> ordercount = new List<double>();
            List<double> numbercount = new List<double>();
            List<double> bedcount = new List<double>();
            List<double> lencount = new List<double>();
            List<double> podcount = new List<double>();
            foreach (DataRow dr in dt.Rows)
            {
                modelcount.Add(Convert.ToDouble(dr["model_count"]));
                ordercount.Add(Convert.ToDouble(dr["order_count"]));
                numbercount.Add(Convert.ToDouble(dr["number"]));
                bedcount.Add(Convert.ToDouble(dr["bed"]));
                lencount.Add(Convert.ToDouble(dr["len"]));
                podcount.Add(Convert.ToDouble(dr["pod"]));
            }
            if (Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount) != null)
            {
                return AssistFun.toJson(Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount));
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据"));
            }
        }

        /// <summary>
        /// 往年统计表
        /// </summary>
        /// <param name="year">年份 </param>
        /// <returns></returns>
        [HttpGet, Route("PastYearStatisticsTable")]
        public HttpResponseMessage FormerYearTable(string year)
        {
            string sql = "";
            //查询出一年当中样板数，生产单数，件数，床数，铺布总长度,特体Pod数的总数和平均数
            sql = "select SUM(model_count) as SumModel,AVG(model_count) as AvgModel,SUM(order_count) as SumOrder,AVG(order_count) as AvgOrder,SUM(number) as SumNumber,SUM(number) as AvgNumber,SUM(bed) as SumBed,AVG(bed) as AvgBed,SUM(len) as SumLen,AVG(len) as AvgLen,SUM(pod) as SumPod,AVG(pod) as AvgPod from Stats_tb  where DATEPART(YEAR,day)=" + year + "";
            SQLCommand sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            List<Number> numbers = AssistFun.DataTableToList<Number>(dt);
            if (numbers != null)
            {
                return AssistFun.toJson(new RespondModel(200, "成功", new data(1, numbers.ConvertAll(c => (object)c))));
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            }
        }

        /// <summary>
        ///本年统计图
        /// </summary>
        ///<returns></returns>
        [HttpGet, Route("CurrentYearStatistics")]
        public HttpResponseMessage ThatYearMap()
        {
            string sql = "";
            //查询本年样板数统计节点
            sql = "SELECT A.newTime,SUM(A.model) AS model FROM (select DateDiff(DAY,time,GETDATE())/14 as newTime,COUNT(*) as model from Model_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) group by DateDiff(DAY,time,GETDATE())/14 " + Model();
            SQLCommand sc = new SQLCommand(sql);
            DataTable model = sc.ExecuteScalars();
            //查询本年生产单数,件数统计节点
            sql = "SELECT A.newTime,SUM(A.orders) AS orders,SUM(A.number) AS number FROM (SELECT DateDiff(DAY, creater_time, GETDATE()) / 14 AS newTime, COUNT(*) as orders, SUM(number) as number from Order_tb group by DateDiff(DAY, creater_time, GETDATE()) / 14" + OrderNumber();
            sc = new SQLCommand(sql);
            DataTable ordernumber = sc.ExecuteScalars();
            //查询本年床数,铺布总长度统计节点
            sql = "SELECT A.newTime,SUM(A.bed) AS bed,SUM(A.len) AS len FROM (select DateDiff(DAY,cut_time,GETDATE())/14 as newTime,COUNT(*) as bed,SUM(lay_count*lay_len) as len from Bed_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,cut_time) group by DateDiff(DAY,cut_time,GETDATE())/14 " + BedLen();
            sc = new SQLCommand(sql);
            DataTable bedlen = sc.ExecuteScalars();
            //查询本年特定Pod数统计节点
            sql = "SELECT A.newTime,SUM(A.pod) AS pod FROM (select DateDiff(DAY,time,GETDATE())/14 as newTime,COUNT(*) as pod from Pod_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time) group by DateDiff(DAY,time,GETDATE())/14 " + Pod();
            sc = new SQLCommand(sql);
            DataTable pod = sc.ExecuteScalars();
            //定义存储对象的数组
            List<int> newTime = new List<int>();
            List<double> modelcount = new List<double>();
            List<double> ordercount = new List<double>();
            List<double> numbercount = new List<double>();
            List<double> bedcount = new List<double>();
            List<double> lencount = new List<double>();
            List<double> podcount = new List<double>();
            int weeks = Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) % 14 == 0 ? Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) / 14 : Convert.ToInt32(DateTime.Now.DayOfYear.ToString()) / 14 + 1;
            List<int> week = new List<int>();
            List<int> diff = new List<int>();
            if (newTime.Count != weeks)
            {
                for (int i = 0; i < weeks; i++)
                {
                    week.Add(i + 1);
                }
                diff = week.Where(c => !newTime.Contains(c)).ToList();
            }

            foreach (DataRow ml in model.Rows)
            {
                modelcount.Add(Convert.ToDouble(ml["model"]));
                newTime.Add(Convert.ToInt32(ml["newTime"]));
            }
            foreach (DataRow or in ordernumber.Rows)
            {
                ordercount.Add(Convert.ToDouble(or["orders"]));
                numbercount.Add(Convert.ToDouble(or["number"]));
            }
            foreach (DataRow bn in bedlen.Rows)
            {
                bedcount.Add(Convert.ToDouble(bn["bed"]));
                lencount.Add(Convert.ToDouble(bn["len"]));
            }
            foreach (DataRow pd in pod.Rows)
            {
                podcount.Add(Convert.ToDouble(pd["pod"]));
            }
            if (Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount) != null)
            {
                return AssistFun.toJson(Choose(modelcount, ordercount, numbercount, bedcount, lencount, podcount));
            }
            else
            {
                return AssistFun.toJson("无数据");
            }
        }

        /// <summary>
        /// 本年统计表
        /// </summary> 
        /// <returns></returns>
        [HttpGet, Route("CurrentYearTable")]
        public HttpResponseMessage ThatYearTable()
        {
            string sql = "";
            //string sql = "select COUNT(*) as SumModel,COUNT(*)/COUNT(DateDiff(DAY,Model_tb.time,GETDATE())/14) as AvgModel,COUNT(*) as SumOrder,COUNT(*)/COUNT(DateDiff(DAY,creater_time,GETDATE())/14) as AvgOrder,SUM(number) as SumNumber,SUM(number)/COUNT(DateDiff(DAY,creater_time,GETDATE())/14) as AvgNumber,COUNT(*) as SumBed,COUNT(*)/COUNT(DateDiff(DAY,lay_time,GETDATE())/14) as AvgBed,SUM(lay_count*lay_len) as SumLen,SUM(lay_count*lay_len)/COUNT(DateDiff(DAY,lay_time,GETDATE())/14) as AvgLen,COUNT(*) as SumPod,COUNT(*)/COUNT(DateDiff(DAY,Pod_tb.time,GETDATE())/14) as AvgPod from Model_tb,Order_tb,Bed_tb,Pod_tb where Model_tb.PLID=Order_tb.o_PLID and Order_tb.o_PLID=Bed_tb.PLID and Order_tb.id=Pod_tb.order_id and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,Model_tb.time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,creater_time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,lay_time) and DATEPART(YYYY,GETDATE())=DATEPART(YYYY,Pod_tb.time)";
            //查询样板数总和及平均样板数
            DataTable model = new DataTable();
            SQLCommand sc = null;
            try
            {
                sql = "select COUNT(*) as SumModel,COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgModel from Model_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time)";
                sc = new SQLCommand(sql);
                model = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                model.Columns.Add(new DataColumn("SumModel"));
                model.Columns.Add(new DataColumn("AvgModel"));
                DataRow row = model.NewRow();
                row["SumModel"] = 0;
                row["AvgModel"] = 0;
                model.Rows.Add(row);
            }

            //查询生产单数总和，平均生产单数，件数总和，平均件数
            DataTable ordernumber = new DataTable();
            try
            {
                sql = "select COUNT(*) as SumOrder,COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgOrder,SUM(number) as SumNumber,SUM(number)/COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgNumber from Order_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,creater_time)";
                sc = new SQLCommand(sql);
                ordernumber = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                ordernumber.Columns.Add(new DataColumn("SumOrder"));
                ordernumber.Columns.Add(new DataColumn("AvgOrder"));
                DataRow row = model.NewRow();
                row["SumOrder"] = 0;
                row["AvgOrder"] = 0;
                ordernumber.Rows.Add(row);
            }

            //查询床数总和，平均床数，铺布总长度总和，平均铺布总长度
            DataTable bedlen = new DataTable();
            try
            {
                sql = "select COUNT(*) as SumBed,COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgBed,SUM(lay_count*lay_len) as SumLen,SUM(lay_count*lay_len)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgLen from Bed_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,cut_time)";
                sc = new SQLCommand(sql);
                bedlen = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                bedlen.Columns.Add(new DataColumn("SumBed"));
                bedlen.Columns.Add(new DataColumn("AvgBed"));
                bedlen.Columns.Add(new DataColumn("SumLen"));
                bedlen.Columns.Add(new DataColumn("AvgLen"));
                DataRow row = bedlen.NewRow();
                row["SumBed"] = 0;
                row["AvgBed"] = 0;
                row["SumLen"] = 0;
                row["AvgLen"] = 0;
                bedlen.Rows.Add(row);
            }

            //查询特定Pod数总和及平均特定Pod数
            DataTable pod = new DataTable();
            try
            {
                sql = "select COUNT(*) as SumPod,COUNT(*)/CEILING(CONVERT(float,datepart(dayofyear,getDate()))/14) as AvgPod from Pod_tb where DATEPART(YYYY,GETDATE())=DATEPART(YYYY,time)";
                sc = new SQLCommand(sql);
                pod = sc.ExecuteScalars();
            }
            catch (Exception)
            {
                pod.Columns.Add(new DataColumn("SumPod"));
                pod.Columns.Add(new DataColumn("AvgPod"));
                DataRow row = pod.NewRow();
                row["SumPod"] = 0;
                row["AvgPod"] = 0;
                pod.Rows.Add(row);
            }

            List<DataTable> numbers = new List<DataTable>() { model, ordernumber, bedlen, pod };
            DataTable dt = new DataTable();
            string tableName = "newTable";
            for (int i = 2; i < numbers.Count; i++)
            {
                if (i == 2)
                {
                    dt = AssistFun.UniteDataTable(model, ordernumber, "newtable");
                    dt = AssistFun.UniteDataTable(dt, numbers[i], tableName);
                }
                else
                {
                    dt = AssistFun.UniteDataTable(dt, numbers[i], tableName);
                }
            }
            List<Number> result = AssistFun.DataTableToList<Number>(dt);


            if (numbers != null)
            {
                return AssistFun.toJson(new { status = 200, tip = "成功", data = new { total = 1, rows = result } });
            }
            else
            {
                return AssistFun.toJson(new { status = 201, tip = "无数据记录", data = "" });
            }
        }

        /// <summary>
        /// 计件统计
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="model">单位类型</param>
        /// <returns></returns>
        [HttpGet, Route("PieceworkStatistics"), JwtAuthActionFilter]
        public HttpResponseMessage PieceStatistical(DateTime year, string model, string userId)
        {
            string token = Request.Headers.Authorization.Parameter;
            int loginId = GetCrrentIDByToken();
            //int loginId = GetCrrentIDByToken("eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJMb2dpbklEIjoxMTIzfQ.I2L2VFJHQkEp50tBeKgDRs4qfiZ-79sEcLDbv96-beU") ;
            SQLCommand sc = new SQLCommand("select power from User_tb where login_id=" + loginId + "");
            int power = int.Parse(sc.ExecuteScalar());
            //查询出员工姓名，计件工资总额
            string years = year.ToString("yyyy");
            string yearss = year.ToString("MM");
            string sql = "";
            if (power == 1)
            {
                if (userId != null)
                {
                    sql = "select UserName ,sum(mymoney) as SumMoney from (select User_tb.name as UserName, (Weight * Task_tb.code) as mymoney from Task_tb, User_tb, Process_tb where User_tb.login_id = Task_tb.user_id and Task_tb.code = Process_tb.code and DATEPART(YYYY,Task_tb.time)=" + years + " and DATEPART(MM,Task_tb.time)=" + yearss + " and User_tb.login_id =" + userId + "";
                    string sqltail = ") a group by a.UserName";
                    sql += PingJieCmd(model) + sqltail;
                }
                else
                {
                    sql = "select UserName ,sum(mymoney) as SumMoney from (select User_tb.name as UserName, (Weight * Task_tb.code) as mymoney from Task_tb, User_tb, Process_tb where User_tb.login_id = Task_tb.user_id and Task_tb.code = Process_tb.code and DATEPART(YYYY,Task_tb.time)=" + years + " and DATEPART(MM,Task_tb.time)=" + yearss + "";
                    string sqltail = ") a group by a.UserName";
                    sql += PingJieCmd(model) + sqltail;
                }
            }
            else
            {
                sql = "select UserName ,sum(mymoney) as SumMoney from (select User_tb.name as UserName, (Weight * Task_tb.code) as mymoney from Task_tb, User_tb, Process_tb where User_tb.login_id = Task_tb.user_id and Task_tb.code = Process_tb.code and DATEPART(YYYY,Task_tb.time)=" + years + " and DATEPART(MM,Task_tb.time)=" + yearss + " and User_tb.login_id =" + loginId + "";
                string sqltail = ") a group by a.UserName";
                sql += PingJieCmd(model) + sqltail;
            }
            sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            List<ShowPiece> pieces = new List<ShowPiece>();
            List<string> name = new List<string>();
            List<double> money = new List<double>();
            if (dt == null)
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            foreach (DataRow dr in dt.Rows)
            {
                name.Add(Convert.ToString(dr["UserName"]));
                money.Add(Convert.ToDouble(dr["SumMoney"]));
            }
            ShowPiece piece = new ShowPiece()
            {
                status = 200,
                data = new Data()
                {
                    total = dt.Rows.Count,
                    X = name,
                    Y = money
                }
            };
            if (piece != null)
            {
                return AssistFun.toJson(piece);
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            }
        }

        /// <summary>
        /// 样板统计饼图
        /// </summary>
        /// <returns></returns>
        [HttpGet, Route("TemplateStatistics")]
        public HttpResponseMessage TemplateStatistics()
        {
            string sql = "";
            SQLCommand sc = null;
            sql = "SELECT COUNT(*) number FROM Order_tb WHERE  creater_time BETWEEN dateadd(DAY,-365,GETDATE()) and GETDATE()";
            sc = new SQLCommand(sql);
            string SumNumber = sc.ExecuteScalar();
            sql = "SELECT TOP 10 COUNT(*) as value,Model_tb.name as name FROM Order_tb LEFT JOIN Model_tb ON Order_tb.o_PLID=Model_tb.PLID WHERE Order_tb.o_PLID=Model_tb.PLID AND Order_tb.creater_time BETWEEN dateadd(DAY,-365,GETDATE()) and GETDATE() GROUP BY Model_tb.name";
            sc = new SQLCommand(sql);
            DataTable dt = sc.ExecuteScalars();
            List<PieModel> piedatas = AssistFun.DataTableToList<PieModel>(dt);
            int other = 0;
            for (int i = 0; i < piedatas.Count; i++)
            {
                other = Convert.ToInt32(SumNumber) - piedatas[i].value;
            }
            piedatas.Add(new PieModel() { value = other, name = "其他" });
            if (piedatas != null)
            {
                return AssistFun.toJson(new RespondModel(200, "成功", new data(piedatas.Count, piedatas.ConvertAll(c => (object)c))));
            }
            else
            {
                return AssistFun.toJson(new RespondModel(201, "无数据记录"));
            }
        }

        /// <summary>
        /// 拼接命令
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private string PingJieCmd(string model)
        {
            string sqlAdd = "";
            if (Convert.ToBoolean(model == "款"))
            {
                sqlAdd = "  and Task_tb.code in (11, 13, 15, 16, 18, 22, 23, 25, 26, 27)";
            }
            else if (Convert.ToBoolean(model == "生产单"))
            {
                sqlAdd = "  and Task_tb.code in (33,42,43,45,46)";
            }
            else if (Convert.ToBoolean(model == "pod"))
            {
                sqlAdd = "  and Task_tb.code in (35)";
            }
            else if (Convert.ToBoolean(model == "件数"))
            {
                sqlAdd = "  and Task_tb.code in (34)";
            }
            else if (Convert.ToBoolean(model == "长度"))
            {
                sqlAdd = "  and Task_tb.code in (106)";
            }
            else if (Convert.ToBoolean(model == "床"))
            {
                sqlAdd = "  and Task_tb.code in (37,48,101,107)";
            }
            else
            {
                sqlAdd = "";
            }
            return sqlAdd;
        }

        /// <summary>
        /// 款式判断
        /// </summary>
        /// <param name="name"></param>
        /// <param name="modelcount"></param>
        /// <param name="ordercount"></param>
        /// <param name="numbercount"></param>
        /// <param name="bedcount"></param>
        /// <param name="lencount"></param>
        /// <param name="podcount"></param>
        /// <returns></returns>
        private ShowCartogram Choose(List<double> modelcount, List<double> ordercount, List<double> numbercount, List<double> bedcount, List<double> lencount, List<double> podcount)
        {
            var array = new int[] { modelcount.Count, ordercount.Count, numbercount.Count, bedcount.Count, lencount.Count, podcount.Count };
            int max = array.Max();
            List<string> name = new List<string> { "样板数", "生产单数", "件数", "床数", "铺布总长度", "特定Pod数" };
            List<LegendInfo> legends = new List<LegendInfo>();
            for (int i = 0; i < name.Count; i++)
            {
                LegendInfo legendinfo = new LegendInfo
                {
                    CompanyName = name[i]
                };
                switch (name[i])
                {
                    case "样板数":
                        legendinfo.Data = modelcount;
                        break;
                    case "生产单数":
                        legendinfo.Data = ordercount;
                        break;
                    case "件数":
                        legendinfo.Data = numbercount;
                        break;
                    case "床数":
                        legendinfo.Data = bedcount;
                        break;
                    case "铺布总长度":
                        legendinfo.Data = lencount;
                        break;
                    case "特定Pod数":
                        legendinfo.Data = podcount;
                        break;
                    default:
                        break;
                }

                legends.Add(legendinfo);
            }
            ShowCartogram cartogram = new ShowCartogram()
            {
                status = 200,
                data = new DataInfo()
                {
                    total = max,
                    legend = name,
                    series = legends
                }
            };
            return cartogram;
        }

        /// <summary>
        /// 本年统计图语句拼接
        /// </summary> 
        /// <returns></returns>
        private string Model()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS model UNION SELECT '24' AS newTime,'0' AS model UNION SELECT '23' AS newTime,'0' AS model UNION SELECT '22' AS newTime,'0' AS model UNION SELECT '21' AS newTime,'0' AS model UNION SELECT '20' AS newTime,'0' AS model UNION SELECT '19' AS newTime,'0' AS model UNION SELECT '18' AS newTime,'0' AS model UNION SELECT '17' AS newTime,'0' AS model";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS model UNION SELECT '15' AS newTime,'0' AS model UNION SELECT '14' AS newTime,'0' AS model UNION SELECT '13' AS newTime,'0' AS model UNION SELECT '12' AS newTime,'0' AS model UNION SELECT '11' AS newTime,'0' AS model UNION SELECT '10' AS newTime,'0' AS model UNION SELECT '9' AS newTime,'0' AS model UNION SELECT '8' AS newTime,'0' AS model UNION SELECT '7' AS newTime,'0' AS model";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS model UNION SELECT '5' AS newTime,'0' AS model UNION SELECT '4' AS newTime,'0' AS model UNION SELECT '3' AS newTime,'0' AS model UNION SELECT '2' AS newTime,'0' AS model UNION SELECT '1' AS newTime,'0' AS model UNION SELECT '0' AS newTime,'0' AS model) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string OrderNumber()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS orders,'0' AS number UNION SELECT '24' AS newTime,'0' AS orders,'0' AS number UNION SELECT '23' AS newTime,'0' AS orders,'0' AS number UNION SELECT '22' AS newTime,'0' AS orders,'0' AS number UNION SELECT '21' AS newTime,'0' AS orders,'0' AS number UNION SELECT '20' AS newTime,'0' AS orders,'0' AS number UNION SELECT '19' AS newTime,'0' AS orders,'0' AS number UNION SELECT '18' AS newTime,'0' AS orders,'0' AS number UNION SELECT '17' AS newTime,'0' AS orders,'0' AS number";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS orders,'0' AS number UNION SELECT '15' AS newTime,'0' AS orders,'0' AS number UNION SELECT '14' AS newTime,'0' AS orders,'0' AS number UNION SELECT '13' AS newTime,'0' AS orders,'0' AS number UNION SELECT '12' AS newTime,'0' AS orders,'0' AS number UNION SELECT '11' AS newTime,'0' AS orders,'0' AS number UNION SELECT '10' AS newTime,'0' AS orders,'0' AS number UNION SELECT '9' AS newTime,'0' AS orders,'0' AS number UNION SELECT '8' AS newTime,'0' AS orders,'0' AS number UNION SELECT '7' AS newTime,'0' AS orders,'0' AS number";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS orders,'0' AS number UNION SELECT '5' AS newTime,'0' AS orders,'0' AS number UNION SELECT '4' AS newTime,'0' AS orders,'0' AS number UNION SELECT '3' AS newTime,'0' AS orders,'0' AS number UNION SELECT '2' AS newTime,'0' AS orders,'0' AS number UNION SELECT '1' AS newTime,'0' AS orders,'0' AS number UNION SELECT '0' AS newTime,'0' AS orders,'0' AS number) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string BedLen()
        {
            string sqlpingjie = "";
            string sqlone = "   UNION SELECT '25' AS newTime,'0' AS bed,'0' AS len UNION SELECT '24' AS newTime,'0' AS bed,'0' AS len UNION SELECT '23' AS newTime,'0' AS bed,'0' AS len UNION SELECT '22' AS newTime,'0' AS bed,'0' AS len UNION SELECT '21' AS newTime,'0' AS bed,'0' AS len UNION SELECT '20' AS newTime,'0' AS bed,'0' AS len UNION SELECT '19' AS newTime,'0' AS bed,'0' AS len UNION SELECT '18' AS newTime,'0' AS bed,'0' AS len UNION SELECT '17' AS newTime,'0' AS bed,'0' AS len";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS bed,'0' AS len UNION SELECT '15' AS newTime,'0' AS bed,'0' AS len UNION SELECT '14' AS newTime,'0' AS bed,'0' AS len UNION SELECT '13' AS newTime,'0' AS bed,'0' AS len UNION SELECT '12' AS newTime,'0' AS bed,'0' AS len UNION SELECT '11' AS newTime,'0' AS bed,'0' AS len UNION SELECT '10' AS newTime,'0' AS bed,'0' AS len UNION SELECT '9' AS newTime,'0' AS bed,'0' AS len UNION SELECT '8' AS newTime,'0' AS bed,'0' AS len UNION SELECT '7' AS newTime,'0' AS bed,'0' AS len";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS bed,'0' AS len UNION SELECT '5' AS newTime,'0' AS bed,'0' AS len UNION SELECT '4' AS newTime,'0' AS bed,'0' AS len UNION SELECT '3' AS newTime,'0' AS bed,'0' AS len UNION SELECT '2' AS newTime,'0' AS bed,'0' AS len UNION SELECT '1' AS newTime,'0' AS bed,'0' AS len UNION SELECT '0' AS newTime,'0' AS bed,'0' AS len) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
        private string Pod()
        {
            string sqlpingjie = "";
            string sqlone = "  UNION SELECT '25' AS newTime,'0' AS pod UNION SELECT '24' AS newTime,'0' AS pod UNION SELECT '23' AS newTime,'0' AS pod UNION SELECT '22' AS newTime,'0' AS pod UNION SELECT '21' AS newTime,'0' AS pod UNION SELECT '20' AS newTime,'0' AS pod UNION SELECT '19' AS newTime,'0' AS pod UNION SELECT '18' AS newTime,'0' AS pod UNION SELECT '17' AS newTime,'0' AS pod";
            string sqltwo = "  UNION SELECT '16' AS newTime,'0' AS pod UNION SELECT '15' AS newTime,'0' AS pod UNION SELECT '14' AS newTime,'0' AS pod UNION SELECT '13' AS newTime,'0' AS pod UNION SELECT '12' AS newTime,'0' AS pod UNION SELECT '11' AS newTime,'0' AS pod UNION SELECT '10' AS newTime,'0' AS pod UNION SELECT '9' AS newTime,'0' AS pod UNION SELECT '8' AS newTime,'0' AS pod UNION SELECT '7' AS newTime,'0' AS pod";
            string sqlthree = "  UNION SELECT '6' AS newTime,'0' AS pod UNION SELECT '5' AS newTime,'0' AS pod UNION SELECT '4' AS newTime,'0' AS pod UNION SELECT '3' AS newTime,'0' AS pod UNION SELECT '2' AS newTime,'0' AS pod UNION SELECT '1' AS newTime,'0' AS pod UNION SELECT '0' AS newTime,'0' AS pod ) A GROUP BY A.newTime ORDER BY A.newTime DESC";
            sqlpingjie = sqlone + sqltwo + sqlthree;
            return sqlpingjie;
        }
    }
}
